﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.OleDb;
using System.IO;
using System.Reflection;
using System.Text.RegularExpressions;

namespace Efficient.Excel
{

    public class ExcelHelper<T> where T : new()
    {
        private IDictionary<Type, IList<IPropertyValueSetter>> entitySetterCache = new Dictionary<Type, IList<IPropertyValueSetter>>();

        public static DataTable ExecuteDataTable(string fileName, string commandText)
        {
            string connectionString = CreateConnectionString(fileName);
            //create & open a SqlConnection, and dispose of it after we are done.
            using (OleDbConnection cn = new OleDbConnection(connectionString))
            {
                cn.Open();
                //call the overload that takes a connection in place of the connection string
                return ExecuteDataTable(cn, commandText);
            }
        }

        

        public static DataTable ExecuteDataTable(OleDbConnection connection, string commandText)
        {
            //create a command and prepare it for execution
            using (OleDbDataAdapter da = new OleDbDataAdapter(commandText, connection))
            {
                DataTable dt = new DataTable();
                //fill the DataSet using default values for DataTable names, etc.
                da.Fill(dt);

                // detach the SqlParameters from the command object, so they can be used again.			
                //return the dataset
                return dt;
            }
        }

        public static DataTable GetTableDataByIndex(string fileName, int index)
        {
            string connectionString = CreateConnectionString(fileName);
            //create & open a SqlConnection, and dispose of it after we are done.
            using (OleDbConnection cn = new OleDbConnection(connectionString))
            {
                cn.Open();
                DataTable dtSheetName = cn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "Table" });
                string[] strTableNames = new string[dtSheetName.Rows.Count];
                for (int k = 0; k < dtSheetName.Rows.Count; k++)
                {
                    strTableNames[k] = dtSheetName.Rows[k]["TABLE_NAME"].ToString();
                }
                if (index > -1 && index < strTableNames.Length)
                {
                    string selectTable = strTableNames[index];
                    string sql = string.Format("select * from [{0}]", selectTable);
                    return ExecuteDataTable(cn, sql);
                }
                throw new IndexOutOfRangeException(string.Concat("不能在上传的文件中找到第:", index, "个工作表"));
                //call the overload that takes a connection in place of the connection string
            }
        }

        private static string CreateConnectionString(string fileName)
        {
            string ext = Path.GetExtension(fileName);
            if (ext == ".xlsx") //Excel2007文件
            {
                return string.Concat("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=", fileName, ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=1;\"");

            }
            return string.Concat("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=", fileName, ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1;\"");
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="filePath"></param>
        /// <param name="index"></param>
        /// <param name="fieldMapping"></param>
        /// <param name="convert"></param>
        /// <returns></returns>
        public IList<T> GetDataEntityByIndex(string filePath, int index)
        {
            return GetDataEntityByIndex(filePath, index, string.Empty, string.Empty, true, null);
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="filePath"></param>
        /// <param name="index"></param>
        /// <param name="distinct"></param>
        /// <returns></returns>
        public IList<T> GetDataEntityByIndex(string filePath, int index, bool distinct)
        {
            return GetDataEntityByIndex(filePath, index, string.Empty, string.Empty, distinct, null);
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="filePath"></param>
        /// <param name="index"></param>
        /// <param name="fieldMapping"></param>
        /// <param name="distinct"></param>
        /// <param name="convert"></param>
        /// <returns></returns>
        public IList<T> GetDataEntityByIndex(string filePath, int index, string fieldMapping, bool distinct, Reader2Entity<T> convert)
        {
            return GetDataEntityByIndex(filePath, index, fieldMapping, string.Empty, distinct, convert);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="filePath"></param>
        /// <param name="index"></param>
        /// <param name="fieldMapping"></param>
        /// <param name="convert"></param>
        /// <returns></returns>
        public IList<T> GetDataEntityByIndex(string filePath, int index, string fieldMapping, Reader2Entity<T> convert)
        {
            return GetDataEntityByIndex(filePath, index, fieldMapping, string.Empty, true, convert);
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="filePath"></param>
        /// <param name="index"></param>
        /// <param name="fieldMapping"></param>
        /// <param name="filter"></param>
        /// <param name="convert"></param>
        /// <returns></returns>
        public IList<T> GetDataEntityByIndex(string filePath, int index, string fieldMapping, string filter, bool distinct, Reader2Entity<T> convert)
        {
            string connectionString = CreateConnectionString(filePath);
            IList<T> result = new List<T>();
            if (string.IsNullOrEmpty(fieldMapping))
            {
                fieldMapping = "*";
            }
            using (OleDbConnection cn = new OleDbConnection(connectionString))
            {
                cn.Open();
                DataTable dtSheetName = cn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "Table" });
                string[] strTableNames = new string[dtSheetName.Rows.Count];
                for (int k = 0; k < dtSheetName.Rows.Count; k++)
                {
                    strTableNames[k] = dtSheetName.Rows[k]["TABLE_NAME"].ToString();
                }
                if (index > -1 && index < strTableNames.Length)
                {
                    string selectTable = strTableNames[index];
                    string sql = string.IsNullOrEmpty(filter) ? string.Format("select {0} from [{1}]", fieldMapping, selectTable) :
                        string.Format("select {0} from [{1}] where {2} ", fieldMapping, selectTable, filter);
                    IDataReader dr = ExecuteReader(cn, CommandType.Text, sql);
                    try
                    {
                        int recordIndex = 0;
                        while (dr.Read())
                        {
                            T entity = new T();
                            if (BeforeImport != null)
                            {
                                ExcelImportArgs args = new ExcelImportArgs(dr, recordIndex);
                                BeforeImport(this, args);
                                if (!args.IsNeed)
                                {
                                    recordIndex++;
                                    continue;
                                }
                            }
                            recordIndex++;
                            if (convert != null)
                            {
                                convert(entity, dr);
                            }
                            else
                            {
                                SetEntityField(entity, dr);
                            }
                            OnConverting(new GenericEventArgs<T>(entity));
                            if (distinct)
                            {
                                if (!result.Contains(entity))
                                {
                                    result.Add(entity);
                                }
                            }
                            else
                            {
                                result.Add(entity);
                            }
                        }
                        return result;
                    }
                    finally
                    {
                        dr.Close();
                    }
                }
                throw new IndexOutOfRangeException(string.Concat("不能在上传的文件中找到第:", index, "个工作表"));
                //call the overload that takes a connection in place of the connection string
            }
        }

        private void SetEntityField(T entity, IDataReader reader)
        {
            const string pattern = @"(?<field>\w+?)\(Type:(?<type>\S+)\{(?<val>\S+)\}\)";
            Regex fieldChecker = new Regex(pattern);
            Type clazz = entity.GetType();
            IList<IPropertyValueSetter> propertyValueSetters = null;
            if (!entitySetterCache.ContainsKey(clazz))
            {
                int fieldCount = reader.FieldCount;
                List<string> readerField = new List<string>();
                propertyValueSetters = new List<IPropertyValueSetter>();
                for (int i = 0; i < fieldCount; i++)
                {
                    readerField.Add(reader.GetName(i));
                }
                PropertyInfo[] propertyList = clazz.GetProperties();
                foreach (PropertyInfo item in propertyList)
                {
                    foreach(string field in readerField)
                    {
                        if (field.Equals(item.Name, StringComparison.OrdinalIgnoreCase))
                        {
                            propertyValueSetters.Add(new PropertyValueSetter(item));
                            break;
                        }
                        else
                        {
                            Match mc = fieldChecker.Match(field);
                            if (mc.Success)
                            {
                                string fieldTitle = mc.Groups["field"].Value;
                                if (item.Name.Equals(fieldTitle, StringComparison.OrdinalIgnoreCase))
                                {
                                    string val = mc.Groups["val"].Value;
                                    propertyValueSetters.Add(new PropertyValueSetter(item, val, mc.Value));
                                    break;
                                }
                            }
                        }
                    }
                }
                entitySetterCache[clazz] = propertyValueSetters;
            }
            propertyValueSetters = entitySetterCache[clazz];
            foreach (PropertyValueSetter setter in propertyValueSetters)
            {
                setter.Execute(entity, reader);
            }
        }



        public static OleDbDataReader ExecuteReader(OleDbConnection conn, CommandType cmdType, string cmdText, params OleDbParameter[] commandParameters)
        {
            OleDbCommand cmd = new OleDbCommand();

            // we use a try/catch here because if the method throws an exception we want to 
            // close the connection throw code, because no datareader will exist, hence the 
            // commandBehaviour.CloseConnection will not work
            try
            {
                PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
                OleDbDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                cmd.Parameters.Clear();
                return rdr;
            }
            catch
            {
                conn.Close();
                throw;
            }
        }

        #region PrepareCommand
        /**/
        /// <summary>
        /// Prepare a command for execution
        /// </summary>
        /// <param name="cmd">OleDbCommand object</param>
        /// <param name="conn">OleDbConnection object</param>
        /// <param name="trans">OleDbTransaction object</param>
        /// <param name="cmdType">Cmd type e.g. stored procedure or text</param>
        /// <param name="cmdText">Command text, e.g. Select * from Products</param>
        /// <param name="cmdParms">OleDbParameters to use in the command</param>
        private static void PrepareCommand(OleDbCommand cmd, OleDbConnection conn, OleDbTransaction trans, CommandType cmdType, string cmdText, OleDbParameter[] cmdParms)
        {

            if (conn.State != ConnectionState.Open)
                conn.Open();

            cmd.Connection = conn;
            cmd.CommandText = cmdText;

            if (trans != null)
                cmd.Transaction = trans;

            cmd.CommandType = cmdType;

            if (cmdParms != null)
            {
                foreach (OleDbParameter parm in cmdParms)
                    cmd.Parameters.Add(parm);
            }
        }

        #endregion

        #region 公共事件

        /// <summary>
        /// 当获得数据后准备导入数据时 触发事件
        /// </summary>
        public event EventHandler<GenericEventArgs<T>> Converting;
        /// <summary>
        /// 当获得数据后准备导入数据时 触发事件
        /// </summary>
        public event EventHandler<ExcelImportArgs> BeforeImport;


        protected virtual void OnConverting(GenericEventArgs<T> e)
        {
            if (Converting != null)
            {
                Converting(this, e);
            }
        }


        #endregion 

        public class ExcelImportArgs : EventArgs
        {
            private readonly IDataReader reader;
            private readonly int index;
            private bool isNeed = true;

            public IDataReader Reader
            {
                get { return reader; }
            }

            public int Index
            {
                get { return index; }
            }
           
            public bool IsNeed
            {
                get { return isNeed; }
                set { isNeed = value; }
            }

            public ExcelImportArgs(IDataReader reader, int index)
            {
                this.reader = reader;
                this.index = index;
            }
        }
    }
}
